﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace BLL
{
    public class MyOracleHelper
    {
        /// <summary>
        /// 获取一张表
        /// </summary>
        /// <param name="strConn">连接语句</param>
        /// <param name="SQL">sql文</param>
        /// <returns></returns>
        public static DataTable ExecuteTable(string strConn, string SQL)
        {
            DataTable dt = new DataTable();
            try
            {
                OracleConnection oracleConnection = new OracleConnection(strConn);
                oracleConnection.Open();
                OracleTransaction oracleTransaction = oracleConnection.BeginTransaction();
                OracleCommand oracleCommand = new OracleCommand();
                oracleCommand.Connection = oracleConnection;
                oracleCommand.Transaction = oracleTransaction;
                oracleCommand.CommandText = SQL;
                OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(oracleCommand);
                oracleDataAdapter.Fill(dt);

                oracleTransaction.Commit();
                oracleConnection.Close();
            }
            catch 
            {
            }
            return dt;
        }


        /// <summary>
        /// 获取表集合
        /// </summary>
        /// <param name="strConn"></param>
        /// <param name="SQLs"></param>
        /// <returns></returns>
        public static DataSet ExecuteSet(string strConn, string[] SQLs)
        {
            DataSet ds = new DataSet();
            try 
            {
                foreach (string s in SQLs)
                {
                    ds.Tables.Add(ExecuteTable(strConn, s));
                }
            }
            catch
            { }
            return ds;
        }



        /// <summary>
        /// 执行SQL 返回影响行数
        /// </summary>
        /// <param name="strConn"></param>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string strConn, string SQL)
        {
            int re = 0;
            try 
            {
                OracleConnection oracleConnection = new OracleConnection(strConn);
                oracleConnection.Open();
                OracleTransaction oracleTransaction = oracleConnection.BeginTransaction();
                OracleCommand oracleCommand = new OracleCommand();
                oracleCommand.Connection = oracleConnection;
                oracleCommand.Transaction = oracleTransaction;
                oracleCommand.CommandText = SQL;
                re = oracleCommand.ExecuteNonQuery();
                oracleTransaction.Commit();
                oracleConnection.Close();
            }
            catch
            {
            }
            return re;
        }


    }
}
